/*
 * Manager.java
 *
 * Created on 3 octobre 2007, 16:30
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package fr.insatoulouse.uvmanager.grb.DataBase;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

import fr.insatoulouse.uvmanager.grb.uvmanager.Assignment;
import fr.insatoulouse.uvmanager.grb.uvmanager.AssignmentStatus;
import fr.insatoulouse.uvmanager.grb.uvmanager.LectureType;
import fr.insatoulouse.uvmanager.grb.uvmanager.UV;
import fr.insatoulouse.uvmanager.grb.uvmanager.UVStatus;

/**
 * Specifies the interface with the database.
 * The database can be configured in the uvmanager.properties file.
 * If the property <i>db.creation</i> is set to <i>recreate</i> all tables are flushed 
 * and recreated.
 * 
 * 
 * @author Alexander Remen
 * @author Trond Bjerkestrand
 */
public class Manager {

	private Connection conn = null;

	/**
	 * Creates a new instance of Manager
	 */
	public Manager() {
		init();
	}

	/***
	 * Initializes the database, creating connection using settings from 
	 * configuration file. 
	 */
	public void init() {
		try {
			// Load properties file
			InputStream in = this.getClass().getClassLoader().getResourceAsStream("uvmanager.properties");
			Properties prop = new Properties();
			try {
				prop.load(in);
			} catch (RuntimeException e) {
				System.out.println("Could not find uvmanager.properties - loading defaults");
				prop.setProperty("db.class", "org.hsqldb.jdbcDriver");
				prop.setProperty("db.url", "jdbc:hsqldb:hsql://localhost/uvmanager");
				prop.setProperty("db.username", "sa");
				prop.setProperty("db.password", "");
				prop.setProperty("db.recreate", "");
			}
			// Load the JDBC driver
			Class.forName(prop.getProperty("db.class").toString()).newInstance();
			conn = DriverManager.getConnection(prop.getProperty("db.url").toString(), 
					prop.getProperty("db.username").toString(),
					prop.getProperty("db.password").toString());
			conn.setAutoCommit(true);
			// If specified in configuration file, delete and rebuild database
			if (prop.getProperty("db.creation").toString().equalsIgnoreCase("recreate")){
				this.deleteTables();
				this.createTables();
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public void close(){
		try {
			Statement statement = conn.createStatement();
			statement.executeQuery("SHUTDOWN");
			statement.close();
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}


	public boolean tableExists(String name) {
		boolean res=false;
		try {
			res = (conn.getMetaData().getTables(null, null, name,
					new String[] { "TABLE" }).next());
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return res;
	}


	public ResultSet execQuery(String query)  {
		ResultSet res=null;
		try {
			Statement statement = conn.createStatement();
			String request = new String(query);
			res=statement.executeQuery(request);
			statement.close();
			conn.commit();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return res;

	}

	public int execUpdate(String update)  {
		int res=0;
		try {
			Statement statement = conn.createStatement();
			String request = new String(update);
			res=statement.executeUpdate(request);
			statement.close();
			conn.commit();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return res;

	}

	/**
	 * Method for creating UV table and ASSIGNMENT table.
	 * @return
	 */
	 public int createTables(){
		 int res=0;

		 // creation of table if it doesn't exist
		 if (this.tableExists("UV"))
			 System.out.println("UV Table exists");
		 else {
			 System.out.println("UV Table does not exist and is going to be created");
			 String sCreation = "CREATE TABLE UV ( ID bigint generated by default as identity(start with 1 , increment by 1) NOT NULL PRIMARY KEY,"
				 + " NAME varchar(30) NOT NULL ," 
				 + " DESCRIPTION varchar(200) NOT NULL ,"
				 + " INCHARGE varchar(30) NOT NULL ,"
				 + " YEAR int,"
				 + " CM int,"
				 + " TD int,"
				 + " TP int,"
				 + " BE int,"
				 + " CONTROLE int,"
				 + " VALIDATED varChar(15) NOT NULL"
				 + ")   ;";
			 this.execQuery(sCreation);
		 }
		 if (this.tableExists("ASSIGNMENT"))
			 System.out.println("ASSIGNMENT Table exists");
		 else {
			 System.out.println("ASSIGNMENT Table does not exist and is going to be created");
			 String sCreation = "CREATE TABLE ASSIGNMENT ( ID bigint generated by default as identity(start with 1 , increment by 1) NOT NULL PRIMARY KEY,"
				 + " UVID BIGINT NOT NULL,"
				 + " TEACHER varchar(15) NOT NULL ,"
				 + " VALIDATED varchar(10) NOT NULL ," 
				 + " AMOUNT int,"
				 + " TYPE varchar(10) NOT NULL"
				 + ")   ;";
			 this.execQuery(sCreation);
			 // UVId is a foreign key referecing ID in the UV table:
			 sCreation ="ALTER TABLE ASSIGNMENT ADD FOREIGN KEY (UVID) REFERENCES UV(ID);";
			 this.execQuery(sCreation);
		 }
		 return res;
	 }

	 /**
	  * Method for deleting the UV table and the ASSIGNMENT table.
	  */
	 public void deleteTables(){
		 System.out.println("Deleting ASSIGNMENT table");
		 String sDelete="DROP TABLE ASSIGNMENT;";
		 this.execQuery(sDelete);
		 System.out.println("Deleting UV table");
		 sDelete="DROP TABLE UV;";
		 this.execQuery(sDelete);
	 }

	 /**
	  * This method adds a row in the UV table and returns the id of the new row.
	  * @param Name
	  * @param Description
	  * @param CM
	  * @param TD
	  * @param TP
	  * @param BE
	  * @param Controle
	  * @param inCharge
	  * @return Id of new UV or -1 if something goes wrong
	  */
	 public int addUV(String Name,String Description,int year,int CM, int TD, int TP, int BE, int Controle,String inCharge){
		 int result=-1;
		 try {
			 PreparedStatement pstmt = conn.prepareStatement("INSERT INTO UV " +
					 "(ID,NAME,DESCRIPTION,INCHARGE,YEAR,CM,TD,TP,BE,CONTROLE,VALIDATED) " +
			 "VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'waitingCO')"); 
			 pstmt.setString(1, Name);
			 pstmt.setString(2, Description);
			 pstmt.setString(3, inCharge);
			 pstmt.setInt(4, year);
			 pstmt.setInt(5, CM);
			 pstmt.setInt(6, TD);
			 pstmt.setInt(7, TP);
			 pstmt.setInt(8, BE);
			 pstmt.setInt(9, Controle);
			 pstmt.execute();

			 // find the last generated id
			 ResultSet res =execQuery("CALL IDENTITY();");
			 res.next();
			 result=res.getInt(1);
		 } catch (SQLException e) {
			 e.printStackTrace();
		 }
		 return result;
	 }

	 /**
	  * Update all fields in a UV except the status.
	  * @param UVId
	  * @param name
	  * @param Description
	  * @param year
	  * @param CM
	  * @param TD
	  * @param TP
	  * @param BE
	  * @param Controle
	  * @param inCharge
	  */
	 public void updateUV(int UVId, String name, String Description,int year,int CM, int TD, int TP, int BE, int Controle,String inCharge){
		 try {
			 PreparedStatement pstmt = conn.prepareStatement("UPDATE UV SET NAME=?, DESCRIPTION = ?,  INCHARGE = ?, YEAR = ?, CM = ?,"
					 +" TD = ?,TP = ?, BE = ?, Controle = ? WHERE ID = ?"); 
			 pstmt.setString(1, name);
			 pstmt.setString(2, Description);
			 pstmt.setString(3, inCharge);
			 pstmt.setInt(4, year);
			 pstmt.setInt(5, CM);
			 pstmt.setInt(6, TD);
			 pstmt.setInt(7, TP);
			 pstmt.setInt(8, BE);
			 pstmt.setInt(9, Controle);
			 pstmt.setInt(10, UVId);
			 pstmt.execute();

		 } catch (SQLException e) {
			 e.printStackTrace();
		 }
	 }

	 /**
	  * Update the UV status
	  * @param UVId
	  * @param status
	  */
	 public void updateUVStatus(int UVId, UVStatus status){
		 try {
			 PreparedStatement pstmt = conn.prepareStatement("UPDATE UV SET VALIDATED = ? WHERE ID = ?"); 
			 pstmt.setString(1, status.toString());
			 pstmt.setInt(2, UVId);
			 pstmt.execute();

		 } catch (SQLException e) {
			 e.printStackTrace();
		 }
	 }


	 /**
	  * This method adds a row in the ASSIGNMENT table and returns the id of the new row.
	  * @param UVId
	  * @param teacher
	  * @param amount
	  * @param type
	  * @return AssignmentID
	  */
	 public int addAssignment(int UVId,String teacher,int amount, String type){
		 int result =-1;
		 try {
			 PreparedStatement pstmt = conn.prepareStatement("INSERT INTO ASSIGNMENT (ID,UVID,TEACHER,VALIDATED,AMOUNT,TYPE) " +
			 "VALUES (NULL, ?, ?, 'waiting', ?, ?)");
			 pstmt.setInt(1, UVId);
			 pstmt.setString(2, teacher);
			 pstmt.setInt(3, amount);
			 pstmt.setString(4, type);
			 pstmt.execute();

			 ResultSet res =this.execQuery("CALL IDENTITY();");

			 res.next();
			 result=res.getInt(1);
		 } catch (SQLException e) {
			 e.printStackTrace();
		 }
		 return result;
	 }

	 /**
	  * To update an assignment with new teacher, amount and type. If it's a new UV, you have to delete this assignment and create a new one. 
	  * @param AssignmentId
	  * @param teacher
	  * @param amount
	  * @param  lectureType.toString()
	  */
	 public void updateAssignment(int AssignmentId,String teacher,int amount, LectureType lectureType){
		 try {
			 PreparedStatement pstmt = conn.prepareStatement("UPDATE ASSIGNMENT SET TEACHER=?, AMOUNT=?, TYPE=? " +
			 "WHERE ID = ?");
			 pstmt.setString(1, teacher);
			 pstmt.setInt(2, amount);
			 pstmt.setString(3,  lectureType.toString());
			 pstmt.setInt(4, AssignmentId);
			 pstmt.execute();
		 } catch (SQLException e) {
			 e.printStackTrace();
		 }
	 }

	 /**
	  * To update the status of an assignment
	  * @param AssignmentId
	  * @param status
	  */
	 public void updateAssignmentStatus(int AssignmentId,AssignmentStatus status){
		 try {
			 PreparedStatement pstmt = conn.prepareStatement("UPDATE ASSIGNMENT SET VALIDATED=?" +
			 "WHERE ID = ?");
			 pstmt.setString(1, status.toString());
			 pstmt.setInt(2, AssignmentId);
			 pstmt.execute();
		 } catch (SQLException e) {
			 e.printStackTrace();
		 }
	 }



	 /**
	  * 
	  * @param id
	  */
	 public void deleteUV(int id){
		 try {
			 PreparedStatement pstmt = conn.prepareStatement("DELETE FROM UV WHERE ID= ?");
			 pstmt.setInt(1, id);
			 pstmt.execute();
		 } catch (SQLException e) {
			 e.printStackTrace();
		 }
	 }

	 /**
	  * 
	  * @param id
	  */
	 public void deleteAssignment(int id){
		 try {
			 PreparedStatement pstmt = conn.prepareStatement("DELETE FROM ASSIGNMENT WHERE ID= ?");
			 pstmt.setInt(1, id);
			 pstmt.execute();
		 } catch (SQLException e) {
			 e.printStackTrace();
		 }

	 }


	 /**
	  * Returns the UV id for the UV specified by name in parameter.
	  * @param name
	  * @return UVId
	  */
	 public int getUVId(String name){
		 int res = -1;
		 try{
			 PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM UV WHERE NAME= ?");
			 pstmt.setString(1, name);
			 ResultSet rs = pstmt.executeQuery();
			 rs.next();
			 res = rs.getInt("ID");
		 }
		 catch (SQLException e) {
			 e.printStackTrace();
		 }
		 return res;
	 }
	 /**
	  * 
	  * @param id
	  * @return UV
	  */
	 public UV getUV(int id){
		 UV uv = null;
		 try {
			 PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM UV WHERE ID= ?");
			 pstmt.setInt(1, id);
			 ResultSet rs = pstmt.executeQuery();
			 rs.next();
			 uv = new UV();
			 uv.setId(rs.getInt("ID"));
			 uv.setName(rs.getString("NAME"));
			 uv.setDescription(rs.getString("DESCRIPTION"));
			 uv.setInCharge(rs.getString("INCHARGE"));
			 uv.setCM(rs.getInt("CM"));
			 uv.setTD(rs.getInt("TD"));
			 uv.setTP(rs.getInt("TP"));
			 uv.setBE(rs.getInt("BE"));
			 uv.setYear(rs.getInt("YEAR"));
			 uv.setControle(rs.getInt("CONTROLE"));
			 uv.setStatus(UVStatus.valueOf(rs.getString("VALIDATED")));
		 } catch (SQLException e) {
			 uv = null;
		 }
		 return uv;
	 }



	 /**
	  * 
	  * @param year
	  * @return ArrayList<UV>
	  */
	 public ArrayList<UV> getPromo(int year){
		 ArrayList<UV> res = new ArrayList<UV>();
		 PreparedStatement pstmt;
		 try {
			 pstmt = conn.prepareStatement("SELECT * FROM UV WHERE YEAR= ?");
			 pstmt.setInt(1, year);
			 ResultSet rs = pstmt.executeQuery();
			 while (rs.next()) {
				 UV uv = new UV();
				 uv.setId(rs.getInt("ID"));
				 uv.setName(rs.getString("NAME"));
				 uv.setDescription(rs.getString("DESCRIPTION"));
				 uv.setInCharge(rs.getString("INCHARGE"));
				 uv.setCM(rs.getInt("CM"));
				 uv.setTD(rs.getInt("TD"));
				 uv.setTP(rs.getInt("TP"));
				 uv.setBE(rs.getInt("BE"));
				 uv.setControle(rs.getInt("CONTROLE"));
				 uv.setStatus(UVStatus.valueOf(rs.getString("VALIDATED")));
				 res.add(uv);
			 }
		 } catch (SQLException e) {
			 System.out.println("Erreur avec promo "+year);
			 e.printStackTrace();
		 }  catch (Exception ex) {
			 ex.printStackTrace();
		 }
		 return res;
	 }

	 public Assignment getAssignment(int assignmentId) {
		 Assignment assignment = null;
		 try {
			 PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM ASSIGNMENT WHERE ID= ?");
			 pstmt.setInt(1, assignmentId);
			 ResultSet rs = pstmt.executeQuery();
			 rs.next();
			 assignment = new Assignment();
			 assignment.setId(rs.getInt("ID"));
			 assignment.setTeacher(rs.getString("TEACHER"));
			 assignment.setUvID(rs.getInt("UVID"));
			 assignment.setType(LectureType.valueOf(rs.getString("TYPE")));
			 assignment.setAmount(rs.getInt("AMOUNT"));
			 assignment.setValidated(AssignmentStatus.valueOf(rs.getString("VALIDATED")));
		 } catch (SQLException e) {
			 assignment = null;
		 }
		 return assignment;
	 }

	 public ArrayList<Assignment> getUVAssignments(int UVId){
		 ArrayList<Assignment> res = new ArrayList<Assignment>();
		 PreparedStatement pstmt;
		 try {
			 pstmt = conn.prepareStatement("SELECT * FROM ASSIGNMENT WHERE UVID= ?");
			 pstmt.setInt(1, UVId);
			 ResultSet rs = pstmt.executeQuery();
			 while (rs.next()) {
				 Assignment assignment = new Assignment();
				 assignment.setId(rs.getInt("ID"));
				 assignment.setTeacher(rs.getString("TEACHER"));
				 assignment.setType(LectureType.valueOf(rs.getString("TYPE")));
				 assignment.setAmount(rs.getInt("AMOUNT"));
				 assignment.setValidated(AssignmentStatus.valueOf(rs.getString("VALIDATED")));
				 assignment.setUvID(UVId);
				 res.add(assignment);
			 }
		 } catch (SQLException e) {
			 e.printStackTrace();
		 }  catch (Exception ex) {
			 ex.printStackTrace();
		 }
		 return res;
	 }



}

